package com.sleep.god.common.utils;

import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;

import java.util.List;
import java.util.Map;

public class ExcelUtils {

    /**
     * toString
     *
     * @param obj
     * @return
     */
    public static String getString(Object obj) {
        if (obj != null && obj != "" && obj != "null") {
            return obj.toString();
        }
        return "";
    }

    /**
     * 将数据放入 workbook
     *
     * @param list       数据集
     * @param dateName   数据字段名 要对应表头名
     * @param headerList 表头名
     * @param sheetName
     * @param columnSize 表头个数
     * @return
     * @throws Exception
     */
    public static HSSFWorkbook createWorkBookFrom(List<Map<String, Object>> list, List<String> dateName, List<String> headerList, String sheetName, int columnSize) throws Exception {
        try {
            if (list == null || list.isEmpty()) {
                throw new Exception("数据为空.");
            }
            //
            HSSFWorkbook wb = new HSSFWorkbook();
            Sheet sheet = wb.createSheet(sheetName);
            sheet.setColumnWidth(0, 3000);
            sheet.setColumnWidth(1, 3000);
            sheet.setColumnWidth(2, 3000);
            sheet.setColumnWidth(3, 3000);
            sheet.setColumnWidth(4, 6000);
            sheet.setColumnWidth(5, 4000);
            //
            HSSFPalette palette = wb.getCustomPalette();
            palette.setColorAtIndex(HSSFColor.HSSFColorPredefined.GREEN.getIndex(), (byte) 0x0D, (byte) 0xBC, (byte) 0x74);
            palette.setColorAtIndex(HSSFColor.HSSFColorPredefined.OLIVE_GREEN.getIndex(), (byte) 0xEB, (byte) 0xFF, (byte) 0xF7);
            palette.setColorAtIndex(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex(), (byte) 0xd9, (byte) 0xd9, (byte) 0xd9);
            // white font
            Font whiteFont = wb.createFont();
            whiteFont.setFontName("微软雅黑");
            whiteFont.setColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
            // black font
            Font blackFont = wb.createFont();
            blackFont.setFontName("微软雅黑");
            blackFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
            // head row, style, background color #0DBC74
            CellStyle headCellStyle = wb.createCellStyle();
            headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            headCellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREEN.getIndex());
            headCellStyle.setFont(whiteFont);
            headCellStyle.setAlignment(HorizontalAlignment.CENTER);
            headCellStyle.setBorderTop(BorderStyle.THIN);
            headCellStyle.setBorderBottom(BorderStyle.THIN);
            headCellStyle.setBorderLeft(BorderStyle.THIN);
            headCellStyle.setBorderRight(BorderStyle.THIN);
            headCellStyle.setLeftBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
            headCellStyle.setRightBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
            headCellStyle.setTopBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
            headCellStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
            // row style, white, text
            CellStyle whiteCellStyle = wb.createCellStyle();
            whiteCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            whiteCellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
            whiteCellStyle.setFont(blackFont);
            whiteCellStyle.setAlignment(HorizontalAlignment.CENTER);
            whiteCellStyle.setBorderLeft(BorderStyle.THIN);
            whiteCellStyle.setBorderRight(BorderStyle.THIN);
            whiteCellStyle.setBorderTop(BorderStyle.THIN);
            whiteCellStyle.setBorderBottom(BorderStyle.THIN);
            whiteCellStyle.setTopBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
            whiteCellStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
            whiteCellStyle.setLeftBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
            whiteCellStyle.setRightBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
            // row style, green, text, background color #EBFFF7
            CellStyle greenCellStyle = wb.createCellStyle();
            greenCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            greenCellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.OLIVE_GREEN.getIndex());
            greenCellStyle.setFont(blackFont);
            greenCellStyle.setAlignment(HorizontalAlignment.CENTER);
            greenCellStyle.setBorderLeft(BorderStyle.THIN);
            greenCellStyle.setBorderRight(BorderStyle.THIN);
            greenCellStyle.setLeftBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
            greenCellStyle.setRightBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
            greenCellStyle.setTopBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
            greenCellStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
            //设置样式
            //int columnSize = 9;
            // head row
            Row headRow = sheet.createRow(0);
            for (int i = 0; i < columnSize; i++) {
                Cell head = headRow.createCell(i);
                head.setCellStyle(headCellStyle);
                headRow.getCell(i).setCellValue(headerList.get(i));
            }
            // data row
            for (int i = 0; i < list.size(); i++) {
                // data
                Map<String, Object> data = list.get(i);
                //// row
                Row row = sheet.createRow(i + 1);
                for (int j = 0; j < columnSize; j++) {
                    Cell cell = row.createCell(j);
                    if (i % 2 == 0) {
                        cell.setCellStyle(whiteCellStyle);
                    } else {
                        cell.setCellStyle(greenCellStyle);
                    }
                    row.getCell(j).setCellValue(getString(data.get(dateName.get(j))));
                }
            }
            return wb;
        } catch (Exception e) {
            throw new Exception("创建 Excel 失败.", e);
        }
    }
    /* //例子
    public ResponseEntity<Resource> exportConf(DeviceAlarmPostConfSearch search
            , @RequestParam(value = "selected[]", required = false) List<Integer> selected) {
        try {
            if (selected != null && selected.size() > 0) {
                search.setIds(selected);
            }
            List<Map<String, Object>> listMapData = deviceAlarmPostConfService.selectMapBSearch(search);
            //表头个数
            int columnSize = 6;
            String sheetName = "预案列表";
            //数据对应的字段名
            List<String> dateName = new ArrayList<>(columnSize);
            dateName.add("name");
            dateName.add("devType");
            dateName.add("logStatus");
            dateName.add("postType");
            dateName.add("postDelay");
            dateName.add("creTime");

            //表头名字
            List<String> headerList = new ArrayList<>(columnSize);
            headerList.add("预案名称");
            headerList.add("预案设备");
            headerList.add("设备状态");
            headerList.add("推送方式");
            headerList.add("推送延迟时间(分钟)");
            headerList.add("创建时间");
            HSSFWorkbook workBook = ExcelUtils.createWorkBookFrom(listMapData, dateName, headerList, sheetName, columnSize);
            //HSSFWorkbook workBook = createWorkBookFrom(list);
            // result
            HttpHeaders headers = new HttpHeaders();
            String contentDisposition = "attachment;filename=" + URLEncoder.encode(sheetName + ".xls", "utf-8");
            headers.add("Content-Disposition", contentDisposition);
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            workBook.write(baos);
            Resource resource = new ByteArrayResource(baos.toByteArray());
            return ResponseEntity.ok().headers(headers).body(resource);
        } catch (Exception e) {
            String msg = e.getMessage();
            Resource resource = new ByteArrayResource(msg.getBytes());
            return ResponseEntity.status(404).contentType(MediaType.APPLICATION_JSON_UTF8).body(resource);
        }
    }
  */
}
